package managers;

import android.content.Context;
import android.database.DatabaseErrorHandler;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.orm.SugarRecord;
import com.xkh.xkh.MyApplication;

import model.OwnUser;

/**
 * 数据库配置类
 * Created by 凯 on 2015/10/19.
 */
public class SQLiteHelper extends SQLiteOpenHelper {

    public static final String db_name = "im.db";

    public SQLiteHelper(int version) {
        this(MyApplication.share(), db_name, null, version);
    }

    public SQLiteHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    public SQLiteHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version, DatabaseErrorHandler errorHandler) {
        super(context, name, factory, version, errorHandler);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        System.out.println("初始化数据库");

        try
        {
            db.beginTransaction();


            // 初始化数据库
            db.execSQL("create table IF NOT EXISTS t_Classify(_id INTEGER,classifyName varchar(50),friendCount INTEGER)");

            db.execSQL("create table IF NOT EXISTS t_Friend(_id INTEGER,friendName varchar(50),realName varchar(50),classifyID INTEGER,classifyName varchar(50),online INTEGER)");
            db.execSQL("create table IF NOT EXISTS t_Classify_Friend(_id INTEGER,classifyID INTEGER,classifyName varchar(50),friendID INTEGER,friendName varchar(50))");
            db.execSQL("create table IF NOT EXISTS t_AddFriend(_id INTEGER,asker_id INTEGER,asker_name varchar(50),be_asked_id INTEGER,be_asked_name varchar(50),message varchar(200),state INTEGER)");
            db.execSQL("create table IF NOT EXISTS t_Message(_id INTEGER,from_id INTEGER,fromName varchar(50),to_id INTEGER,toName varchar(50)," +
                    "fromMe INTEGER,type INTEGER,fileURI varchar(200),fileName varchar(50),local INTEGER,text varchar(200),timestamp INTEGER )");

            db.execSQL("create table IF NOT EXISTS t_Group(_id INTEGER,groupName varchar(50),createTime varchar(50),ownerID INTEGER,ownerName varchar(50),userCount INTEGER)");
            db.execSQL("create table IF NOT EXISTS t_GroupUser(_id INTEGER,groupName varchar(50),groupID INTEGER,userID INTEGER,friendName varchar(50),realName varchar(50))");

            db.execSQL("create table IF NOT EXISTS t_GroupMessage(_id INTEGER,fromID INTEGER,GroupID INTEGER,fromName varchar(50),GroupName varchar(50)," +
                    "type INTEGER,fileURI varchar(200),fileName varchar(50),local INTEGER,text varchar(200),timestamp INTEGER)");

            db.execSQL("create table IF NOT EXISTS t_view_recent(account varchar(50),realName varchar(50),content varchar(200),time INTEGER,avar varchar(200),type INTEGER,unread INTEGER)");

            // 触发器，分组防止重复插入
       /*
        db.execSQL("CREATE TRIGGER [classify_Insert] \n" +
                "BEFORE INSERT ON [t_Classify] \n" +
                "FOR EACH ROW \n" +
                "BEGIN \n" +
                "\n" +
                "select raise(rollback,'已存在')\n" +
                "    where (select _id from t_Classify  where _id = new._Id ) is not null;\n" +
                "\n" +
                "END");*/

            // 触发器，重复插入，则删除重复数据
            db.execSQL("CREATE TRIGGER if not exists [classify_Insert2] \n" +
                    "BEFORE INSERT ON [t_Classify] \n" +
                    "FOR EACH ROW \n" +
                    "BEGIN \n" +
                    "\n" +
                    "delete from t_Classify where _id = new._id;\n" +
                    "\n" +
                    "END");

            // 触发器，重复插入，删除好友请求重复数据
            db.execSQL("CREATE TRIGGER if not exists [addFriend_insert] \n" +
                    "BEFORE INSERT ON [t_addFriend]\n" +
                    "FOR EACH ROW \n" +
                    "BEGIN \n" +
                    "\n" +
                    "delete from t_addFriend where _id = new._id;\n" +
                    "\n" +
                    "END");
            // 触发器，防止好友重复插入
            db.execSQL("CREATE TRIGGER if not exists [friend_Insert]\n" +
                    "BEFORE INSERT ON [t_Friend]\n" +
                    "FOR EACH ROW\n" +
                    "BEGIN\n" +
                    "\n" +
                    "delete from t_Friend where _id = new._id;\n" +
                    "\n" +
                    "END");

            // 闸门的基本信息
            db.execSQL("create table if not exists t_IrrBAWaterGate (watergateCode varchar(50),canalCode varchar(50),watergateName varchar(50)," +
                    "watergateType varchar(50),watergateMET varchar(50),watergateLC varchar(50),watergateNUM varchar(20),watergateHeight varchar(20)," +
                    "watergateWtdth varchar(20),watergateQ varchar(20),watergateMTP varchar(20),watergateStatus varchar(50),engManCd varchar(50)," +
                    "telephone varchar(50),timeSign varchar(50),remark varchar(256),stadiaCode varchar(50),designWaterLevel varchar(50),gaocheng varchar(50),orderNum int)");

            // 测站基本信息
            db.execSQL("create table if not exists t_IrrBRStadia (stadiaCode varchar(50),stadiaName varchar(50),stadiaType varchar(50),locCode varchar(50)," +
                    "stbk varchar(50),flowDir varchar(50),datTp varchar(50),datElev varchar(50),modBasVal varchar(50),modPara varchar(50)," +
                    "estStYm varchar(50),runCond varchar(50),engManCd varchar(50),timeSign varchar(50),remark varchar(256),elevation varchar(50))");

            // 渠道基本信息
            db.execSQL("create table if not exists t_IrrBTCanalDetail (canalCode varchar(50),canalName varchar(50),canalG varchar(10),canalDesQ varchar(20)," +
                    "leakR varchar(20),canalLen varchar(20),lineLen varchar(20),engManCd varchar(50),uperCanalCd varchar(50),timeSign varchar(50),remark varchar(256),stadiaCode varchar(50))");


            db.setTransactionSuccessful();
        }catch (Exception e)
        {
            e.printStackTrace();
        }finally {
            db.endTransaction();
        }


    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        System.out.println("升级数据库");

//        SugarRecord.deleteAll(OwnUser.class);
        // 升级数据库
        onCreate(db);
    }

    // 删除数据库
    public void delete() {
        MyApplication.share().deleteDatabase(db_name);
    }
}
